Imports ADOConnection

Public Class frmChonKyKiemKe
    Private m_nhakho As String = ""
    Private m_loaikho As String = ""

    Private Sub frmChonKyKiemKe_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        KhoiTaocbbKyKiemKe()
    End Sub

    Public Sub KhoiTaocbbKyKiemKe()
        cbbKyKiemKe.Items.Clear()
        cbbKyKiemKe.Items.Add("Tháng 1")
        Dim strsql As String
        strsql = "select count(*) from tblDPKK where Year(ngayKK) = '" & CStr(Now.Year) & "'"
        Dim value As Integer
        value = BaseDB.ExecSql_DataValue(strsql)
        If (value <> 0) Then
            btnKiemKeThang7.Enabled = False
            cbbKyKiemKe.Items.Add("Tháng 7")
            Dim NguoiKK As String
            Dim ngaykk As Date
            ngaykk = BaseDB.ExecSql_DataValue("select NgayKK from tblDPKK where Year(ngayKK) = '" & CStr(Now.Year) & "'")
            NguoiKK = BaseDB.ExecSql_DataValue("select fullname from tblUSER where user_id in (select nguoikk from tblDPKK where Year(ngayKK) = '" & CStr(Now.Year) & "')")
            lblKiemKe.Text = "Đã kiểm kê tháng 7" & Chr(10) & "Người kiểm kê: " & NguoiKK & Chr(10) & "Ngày kiểm kê: " & ngaykk.ToString("dd/MM/yyyy")
        Else
            lblKiemKe.Visible = False
        End If
    End Sub

    Private Sub btnKiemKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKiemKe.Click
        If (cbbKyKiemKe.SelectedIndex <> -1) Then
            Dim kykiemke As Boolean
            If (cbbKyKiemKe.SelectedIndex = 0) Then
                kykiemke = True
            Else
                kykiemke = False
            End If
            Dim frm As New frmKiemKe(m_nhakho, m_loaikho, kykiemke)
            frm.ShowDialog()
        Else
            ShowMessages("Bạn chưa chọn kỳ cần kiểm kê")
        End If
    End Sub

#Region "Thuc Hien Kiem Ke Thang 7"
    Private Sub btnKiemKeThang7_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnKiemKeThang7.Click
        ThucHienKiemKeThang7()
    End Sub

    Private Sub ThucHienKiemKeThang7()
        Dim strsql As String
        Dim value As Integer
        'kiemtra tai chinh da xac nhan het chua?
        strsql = "select count(*) from tblDPNX where KYQT = '1'"
        value = BaseDB.ExecSql_DataValue(strsql)
        If (value > 0) Then
            MsgBox("Còn " & value.ToString & " phiếu chưa được quyết toán")
            Exit Sub
        End If
        'chon tat ca cac lo hang co sltonth > 0 

        strsql = "select count(*) from tblDPKK where Year(ngayKK) = '" & CStr(Now.Year) & "'"

        value = BaseDB.ExecSql_DataValue(strsql)
        If (value <> 0) Then
            MsgBox("Đã thực hiện kiểm kê giữa kì rồi.")
            Exit Sub
        End If
        'chi sua sltt, hkk, lydo
        'co the sua slss, dieuchinh lam sau neu yeu cau
        strsql = "select mathang_id,h,sltonkh,slss11,slkk11,hkk1 from tblTON " & _
                "where sltonkh > 0"
        Dim objReader As CDataReader = Nothing
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objUtility As IDBUtility = objConnection.DBUtility
        Dim objCommand As IDbCommand = objConnection.CreateCommand(strsql)
        Try
            objReader = objConnection.GetDataReader(objCommand, True)
            While objReader.Read
                Dim objItem As New CMatHang
                With objItem
                    .MatHang_ID = objReader.GetDecimal(0)
                    '.HKK7 = objReader.GetString(1) '= h
                    .SLSS17 = objReader.GetDecimal(2) '= sltonkh
                    .SLKK17 = 0 'mac dinh = 0
                    .SLSS11 = objReader.GetDecimal(3) 'khong doi
                    .SLKK11 = objReader.GetDecimal(4) ' khong doi
                    .HKK1 = objReader.GetString(5) ' khong doi
                    'them cac truong con lai trong
                End With
                'update
                CMatHangs.UpdateKiemKe(objItem)
            End While
            'insert vao tblDPKK nguoi da thuc hien kiem ke va ngay kk
            InsertDPKK(g_User.User_ID)
        Catch ex As System.Exception
            MsgBox(strsql)
            'Throw ex
        Finally
            If Not IsNothing(objReader) Then
                objReader.Close()
            End If
            objConnection.Close()
        End Try
        MsgBox("Thành công")
        'refesh lai du lieu (cbbKyKiemKe)
        KhoiTaocbbKyKiemKe()
    End Sub

    Public Sub InsertDPKK(ByVal ma_user As Integer)
        Dim objConnection As IADOConnection = g_objConnFactory.GetConnection
        Dim objDBUtility As IDBUtility = objConnection.DBUtility

        'Dim sInsert As String = objStringBuilder.ToString
        Dim sInsert As String = "INSERT INTO tblDPKK" & _
        " (ngayKK, nguoiKK) VALUES(" & _
        objDBUtility.GetParamPlaceHolder("NgayKiemKe", True) & _
        objDBUtility.GetParamPlaceHolder("NguoiKiemKe", False) & _
        ")"

        Dim objCommand As IDbCommand = objConnection.CreateCommand(sInsert)
        Dim objTransaction As IDbTransaction = objConnection.BeginTransaction
        Dim lRowsAffected As Long = 0

        Try
            With objCommand
                .Transaction = objTransaction
                .Parameters.Add(objDBUtility.CreateParameter("NgayKiemKe", DbType.Date, Now.Date, 0))
                .Parameters.Add(objDBUtility.CreateParameter("NguoiKiemKe", DbType.Int32, ma_user, 0))

                'Call objDBUtility.Prepare(objCommand)
                lRowsAffected = .ExecuteNonQuery()

            End With

            'lSerialKey = objDBUtility.InsertAndReturnSerialKey(objCommand, "DoiTuong_ID", "DoiTuong")
            objTransaction.Commit()

        Catch ex As System.Exception
            objTransaction.Rollback()
            'Throw ex
        Finally
            objConnection.Close()
        End Try
        'Return lRowsAffected
    End Sub
#End Region

#Region "txtLoaiKho,txtNhaKho"

    Private Sub txtNhaKho_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtNhaKho.DoubleClick
        HienDMNhaKho()
    End Sub
    Private Sub txtNhaKho_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtNhaKho.KeyDown
        If (e.KeyCode = Keys.Enter) Or (e.KeyCode = Keys.F1) Then
            HienDMNhaKho()
        ElseIf (e.Modifiers = Keys.Control) And (e.KeyCode = Keys.Delete) Then
            txtNhaKho.Text = ""
            m_nhakho = ""
        End If
    End Sub

    Private Sub txtLoaiKho_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtLoaiKho.DoubleClick
        HienDMLoaiKho()
    End Sub

    Private Sub txtLoaiKho_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtLoaiKho.KeyDown
        If (e.KeyCode = Keys.Enter) Or (e.KeyCode = Keys.F1) Then
            HienDMLoaiKho()
        ElseIf (e.Modifiers = Keys.Control) And (e.KeyCode = Keys.Delete) Then
            txtLoaiKho.Text = ""
            m_loaikho = ""
        End If
    End Sub

    Public Sub HienDMNhaKho()
        Dim strDMNhaKho As String
        strDMNhaKho = "select KH,TEN from tblDMKhac where L = 'K'"
        Dim ma, ten As String
        ma = ""
        ten = ""
        If (txtNhaKho.Text <> "") Then
            Dim strSql As String
            strSql = "select TEN from tblDMKhac where L = 'K' and KH = '" & txtNhaKho.Text & "'"
            Dim ten_nhakho As String
            ten_nhakho = BaseDB.ExecSql_DataValue(strSql)
            If (Not ten_nhakho Is Nothing) Then
                m_nhakho = txtNhaKho.Text
                txtNhaKho.Text = ten_nhakho
            Else
                'hien DM LoaiKho
                HienDM(strDMNhaKho, "TEN", "KH", ten, ma)
                m_nhakho = ma
                txtNhaKho.Text = ten
            End If
        Else
            'hien DM LoaiKho
            HienDM(strDMNhaKho, "TEN", "KH", ten, ma)
            m_nhakho = ma
            txtNhaKho.Text = ten
        End If
    End Sub

    Public Sub HienDMLoaiKho()
        Dim strDMLoaiKho As String
        strDMLoaiKho = "select KH,TEN from tblDMKhac where L = 'L'"
        Dim ma, ten As String
        ma = ""
        ten = ""
        If (txtLoaiKho.Text <> "") Then
            Dim strSql As String
            strSql = "select TEN from tblDMKhac where L = 'L' and KH = '" & txtLoaiKho.Text & "'"
            Dim ten_loaikho As String
            ten_loaikho = BaseDB.ExecSql_DataValue(strSql)
            If (Not ten_loaikho Is Nothing) Then
                m_loaikho = txtLoaiKho.Text
                txtLoaiKho.Text = ten_loaikho
            Else
                'hien DM LoaiKho
                HienDM(strDMLoaiKho, "TEN", "KH", ten, ma)
                m_loaikho = ma
                txtLoaiKho.Text = ten
            End If
        Else
            'hien DM LoaiKho
            HienDM(strDMLoaiKho, "TEN", "KH", ten, ma)
            m_loaikho = ma
            txtLoaiKho.Text = ten
        End If
    End Sub

    Public Sub HienDM(ByVal strsql As String, ByVal strDM As String, ByVal strVM As String, _
                ByRef DM As String, ByRef VM As String)
        Dim frm As New frmChonDanhMucTheoListBox(strsql, strDM, strVM, "")
        frm.ShowDialog()
        DM = frm.DisplayMember
        VM = frm.ValueMember
    End Sub
#End Region

End Class